IF exists(SELECT * FROM dbo.sysobjects WHERE name='r_XMLtrimesterrapportCijfers' AND xtype='P') 
BEGIN
	DROP PROCEDURE r_XMLtrimesterrapportCijfers
END
GO

CREATE    procedure r_XMLtrimesterrapportCijfers(@inschrijvingsNr char(7), @jaar int)
as
set nocount on

-----------------  VOORAF  -----------------------------------
set @jaar = isNull(@jaar,0)
set @inschrijvingsNr = isNull(@inschrijvingsNr,'')
declare @achternaam varchar(50), @voornaam varchar(50), @klas varchar(5)
select @achternaam = achternaam, 
	@voornaam = voornaam,
	@klas = klas
from leerlingen..leerlingen 
where inschrijvingsnr = @inschrijvingsnr

set @achternaam = isNull(@achternaam,'')
set @voornaam = isNull(@voornaam,'')
set @klas = isNull(@klas,'')

create table #tussen( tekst varchar(5000))
create table #vakken( volgNummer char(2),vak char(2),vakNaam varchar(50))
create table #groepen(groepscode varchar(5))

-- De vakken worden ingevuld:

insert into #vakken(volgNummer,vak,vaknaam)
select distinct volgnummer,vakcode,vaknaam
  from leraars.dbo.alleIndividueleVakcodes(@inschrijvingsNr)
  order by volgnummer


-----------------  OPBOUW DOCUMENT  -----------------------------------
insert into #tussen(tekst)
values('<?xml version="1.0"  encoding="ISO-8859-1" ?>')

-- De juiste stijl-pagina wordt aan de XML-pagina gelinkt.
	
insert into #tussen(tekst)
values('<?xml-stylesheet type="text/xsl" href="rapport2periodes.xsl" ?>')
insert into #tussen(tekst)
values('<trimRapport><hoofding>')
insert into #tussen(tekst)
values('<titel>' + @voorNaam + ' ' + @achternaam + ' Semesterresultaten </titel>')
insert into #tussen(tekst)
values('<leerling>' + @voorNaam + ' ' + @achternaam + '</leerling>')
insert into #tussen(tekst)
values('<comBestand>' + @inschrijvingsNr + 'c.xml</comBestand>')
insert into #tussen(tekst)
values('<periode>Semesterresultaten' 
	+ ' - Schooljaar ' + convert(varchar(4),@jaar) + ' - ' +
	+ convert(varchar(4),@jaar + 1) + '</periode>')
insert into #tussen(tekst)
values('</hoofding><vakken>')

--------------------------------------------------------------
--  Opbouw vakresultaten
--------------------------------------------------------------
  insert into #tussen(tekst)
  select '<vak naam="' + v.vakNaam + '"><periode nr="1"><d>' 
	+ case when p.dw1 is not null and (p.dw1 >= 0) 
	       then convert(varchar(5),p.dw1) else '' end 
	+ '</d><e>'
	+ case when p.ex1 is not null and (p.ex1 >= 0) 
	       then convert(varchar(5),p.ex1) else '' end 
	+ '</e><t>'
	+ case when p.tot1 is not null and (p.tot1 >= 0) 
	       then convert(varchar(5),p.tot1) else '' end 
	+ '</t></periode>'

	+ '<periode nr="2"><d>'
	+ case when p.dw2 is not null and (p.dw2 >= 0) 
	       then convert(varchar(5),p.dw2) else '' end 
	+ '</d><e>'
	+ case when p.ex2 is not null and (p.ex2 >= 0) 
	       then convert(varchar(5),p.ex2) else '' end 
	+ '</e><t>'
	+ case when p.tot2 is not null and (p.tot2 >= 0) 
	       then convert(varchar(5),p.tot2) else '' end 
	+ '</t></periode>'

	+ '<periode nr="j"><d>'
	+ case when p.dw4 is not null and (p.dw4 >= 0) 
	       then convert(varchar(5),p.dw4) else '' end 
	+ '</d><e>'
	+ case when p.ex4 is not null and (p.ex4 >= 0) 
	       then convert(varchar(5),p.ex4) else '' end 
	+ '</e><t>'
	+ case when p.tot4 is not null and (p.tot4 >= 0) 
	       then convert(varchar(5),p.tot4) else '' end 
	+ '</t></periode>'

	+ '<periode nr="g"><d>'
	+ case when p.dw4gem is not null and (p.dw4gem >= 0) 
	       then convert(varchar(5),p.dw4gem) else '' end 
	+ '</d><e>'
	+ case when p.ex4gem is not null and (p.ex4gem >= 0) 
	       then convert(varchar(5),p.ex4gem) else '' end 
	+ '</e><t>'
	+ case when p.tot4gem is not null and (p.tot4gem >= 0) 
	       then convert(varchar(5),p.tot4gem) else '' end 
	+ '</t></periode>'
	+ '</vak>'
  from #vakken as v 
	left join (select * from r_trimestercijfersVoorRapport 
	where inschrijvingsNr = @inschrijvingsNr) as p
	on v.vak = p.vakcode
  order by v.volgNummer

  -- De leerlingtotalen worden ingevoegd
  insert into #tussen(tekst)
  values ('<totaal>')

  insert into #tussen(tekst)
  select '<periode nr="1"><d>' 
	+ case when p.dw1 is not null and (p.dw1 >= 0) 
	       then convert(varchar(5),p.dw1) else '' end 
	+ '</d><e>'
	+ case when p.ex1 is not null and (p.ex1 >= 0) 
	       then convert(varchar(5),p.ex1) else '' end 
	+ '</e><t>'
	+ case when p.tot1 is not null and (p.tot1 >= 0) 
	       then convert(varchar(5),p.tot1) else '' end 
	+ '</t></periode>'

	+ '<periode nr="2"><d>'
	+ case when p.dw2 is not null and (p.dw2 >= 0) 
	       then convert(varchar(5),p.dw2) else '' end 
	+ '</d><e>'
	+ case when p.ex2 is not null and (p.ex2 >= 0) 
	       then convert(varchar(5),p.ex2) else '' end 
	+ '</e><t>'
	+ case when p.tot2 is not null and (p.tot2 >= 0) 
	       then convert(varchar(5),p.tot2) else '' end 
	+ '</t></periode>'

	+ '<periode nr="j"><d>'
	+ case when p.dw4 is not null and (p.dw4 >= 0) 
	       then convert(varchar(5),p.dw4) else '' end 
	+ '</d><e>'
	+ case when p.ex4 is not null and (p.ex4 >= 0) 
	       then convert(varchar(5),p.ex4) else '' end 
	+ '</e><t>'
	+ case when p.tot4 is not null and (p.tot4 >= 0) 
	       then convert(varchar(5),p.tot4) else '' end 
	+ '</t></periode>'

	+ '<periode nr="g"><d>'
	+ case when p.dw4gem is not null and (p.dw4gem >= 0) 
	       then convert(varchar(5),p.dw4gem) else '' end 
	+ '</d><e>'
	+ case when p.ex4gem is not null and (p.ex4gem >= 0) 
	       then convert(varchar(5),p.ex4gem) else '' end 
	+ '</e><t>'
	+ case when p.tot4gem is not null and (p.tot4gem >= 0) 
	       then convert(varchar(5),p.tot4gem) else '' end 
	+ '</t></periode>'
   from r_trimestertotaalVoorRapport as p
   where inschrijvingsNr = @inschrijvingsNr

   insert into #tussen(tekst)
   values ('</totaal>')

  -- Afsluiting

  insert into #tussen(tekst)
  values ('</vakken>')

  insert into #tussen(tekst)
  values('</trimRapport>')

select * from #tussen